# -*- coding: utf-8 -*-
import BeautifulSoup
import urllib2
import sqlite3
import csv



#Returns all found tags
def tagToFind(soupVar, tagName, single):
    if(single is False):
        return soupVar.findAll(tagName)
    return soupVar.find(tagName)

def getNumberOfTags(soupVar):
    tagArray = ['h1', 'h2', 'h3', 'div', 'p', 'ul', 'ol']
    _temp = map(lambda x: "Tag '{0}' has {1} occurrences on this URL".format(x, len(tagToFind(soupVar, x))), tagArray) # Number of times that a tag is used

def getTypeOfStabs():
    stabArray = []
    for f in range(0, 11):
        year = ("0" + str(f)) if len(str(f)) == 1 else str(f)
        url = urllib2.urlopen("http://www.rebides.oces.mctes.pt/Rebides{0}/".format(year))
        # Encontra a div em que o atributo class tem o valor de 'instituicao' e de seguinda encontra todas as tags 'a' dentro dessa div
        soup = BeautifulSoup.BeautifulSoup(url).find('div', {'class':'instituicao'})
        soup = soup.findAll('a')
        for k in soup:
            if k.contents not in stabArray:
                stabArray.append(k.contents)
    return stabArray

def createTables(dbConnection):
    dbCursor = dbConnection.cursor()
    for k in getTypeOfStabs():
        dbCursor.execute(u""" CREATE TABLE '{0}' (codDocente, nomeDocente, grauAcademico, curso, catDocente, regTemp, anoLectivo, nEstabelecimento) """.format(str(k[0]).decode("utf-8")))
    dbConnection.commit()
    dbCursor.close()

def insertIntoTable(dbConnection):
    dbCursor = dbConnection.cursor()
    for k in range(0, 11):
        fileReader = csv.reader(open("csv_info/reb{0}.csv".format(k)), delimiter=',')
        for k in fileReader:
            info = [str(k[0]).decode("utf-8"), str(k[1]).replace("'", ".").decode("utf-8"), str(k[2]).replace("'", ".").decode("utf-8"), str(k[3]).replace("'", ".").decode("utf-8"),
                    str(k[4]).decode("utf-8").replace("'", "."), str(k[5]).replace("'", ".").decode("utf-8"), str(k[6]).replace("'", ".").decode("utf-8"),
                    str(k[7]).replace("'", ".").decode("utf-8"), str(k[8]).decode("utf-8")]
            if k[0].isdigit():
                dbCursor.execute("""INSERT INTO '{7}' VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{8}')""".decode('utf-8').format(*info).encode("utf-8"))
    dbConnection.commit()

def nTeacherPerStabInfo(dbConnection, tStab):
    dbCursor = dbConnection.cursor()
    tStab = tStab.decode("utf-8")
    dbCursor.execute("SELECT * FROM \"Ensino Superior Público Universitário\"");
    dbConnection.commit()
    sqlData = dbCursor.fetchall();

    teacherDataPerYear = {}
    
    stabNames = list(set(map(lambda x: x[7], sqlData)))
    year = None
    for year in range(2000, 2011):
        for k in stabNames:
            n = len(filter(lambda x: (x.count(k) == 1 and x.count(str(year)) == 1), sqlData))
            if n == 0:
                teacherDataPerYear[k + str(year)] = len(filter(lambda x: repr(x[7]) == repr(k), sqlData))
            else:
                 teacherDataPerYear[k + str(year)] = n
    return teacherDataPerYear

def teacherPerStabInfo(dbConnection, tStab, stabName):
    dbCursor = dbConnection.cursor()
    tStab = tStab.decode("utf-8")
    dbCursor.execute("SELECT * FROM \"Ensino Superior Público Universitário\"");
    dbConnection.commit()
    sqlData = dbCursor.fetchall();

    teacherDataPerYear = {}
    
    stabNames = list(set(map(lambda x: x[7], sqlData)))
    year = None
    for year in range(2000, 2011):
        dbCursor.execute("SELECT nomeDocente FROM \"Ensino Superior Público Universitário\" WHERE nEstabelecimento=\"{0}\" AND anoLectivo=\"{1}\"".format(stabName.encode("utf-8"), str(year).encode("utf-8")))
        dbConnection.commit()
        teacherDataPerYear[stabName + str(year)] =  dbCursor.fetchall()
    return teacherDataPerYear

def nTeacherCategoryPerStabInfo(dbConnection, tStab):
    dbCursor = dbConnection.cursor()
    tStab = tStab.decode("utf-8")
    dbCursor.execute("SELECT * FROM \"Ensino Superior Público Universitário\"");
    dbConnection.commit()
    sqlData = dbCursor.fetchall();
    
    teacherCatPerYear = {}
    catNames = list(set(map(lambda x: x[4], sqlData)))
    for year in range(2000, 2011):
        for k in catNames:
            n = len(filter(lambda x: (x.count(k) == 1 and x.count(str(year)) == 1), sqlData))
            if n == 0:
                teacherCatPerYear[k] = len(filter(lambda x: repr(x[4]) == repr(k), sqlData))
            else:
                 teacherCatPerYear[k] = n
    return teacherCatPerYear


def nTeacherDegreePerStabInfo(dbConnection, tStab):
    dbCursor = dbConnection.cursor()
    tStab = tStab.decode("utf-8")
    dbCursor.execute("SELECT * FROM \"Ensino Superior Público Universitário\"");
    dbConnection.commit()
    sqlData = dbCursor.fetchall();
    
    teacherDegreePerYear = {}
    degreeNames = list(set(map(lambda x: x[2], sqlData)))
    for year in range(2000, 2011):
        for k in degreeNames:
            n = len(filter(lambda x: (x.count(k) == 1 and x.count(str(year)) == 1), sqlData))
            if n == 0:
                teacherDegreePerYear[k] = len(filter(lambda x: repr(x[2]) == repr(k), sqlData))
            else:
                 teacherDegreePerYear[k] = n
    return teacherDegreePerYear

dbConnection = sqlite3.connect('rebides.db3')
#createTables(dbConnection)
#insertIntoTable(dbConnection)
print teacherPerStabInfo(dbConnection, "Ensino Superior Público Universitário", "Universidade de Aveiro")
dbConnection.close()





